package com.albertzhe.util01;


import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;


public class JdbcUtilsDao {
    private final static String USERNAME = "";
    private final static String PASSWORD = "";
    private final static String DRIVER = "";
    private final static String URL = "";

    private Connection connection = null;
    private PreparedStatement pstmt = null;
    private ResultSet resultSet = null;

    /**
     * 构造方法，并加载驱动
     */
    public JdbcUtilsDao() throws ClassNotFoundException {
        Class.forName(DRIVER);
    }

    /**
     * 获得数据库的连接
     */
    public Connection getConnection() throws SQLException {
        this.connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        return connection;
    }

    /**
     * 释放链接
     */
    private void release() throws SQLException {
        if (Objects.nonNull(resultSet)) {
            resultSet.close();
        }
        if (Objects.nonNull(pstmt)) {
            pstmt.close();
        }
    }


    /**
     * 关闭连接
     */
    public void closeConn() throws SQLException {
        if (Objects.nonNull(connection)) {
            connection.close();
        }
    }

    /**
     * 增加、删除、改 操作
     *
     * @param sql    使用占位符的 sql 语句
     * @param params 按顺序填充的值
     */
    public int update(String sql, List<Object> params) throws SQLException {
        pstmt = connection.prepareStatement(sql);
        int index = 1;
        for (Object param : params) {
            pstmt.setObject(index++, param);
        }
        int result = pstmt.executeUpdate();
        this.release();
        return result;
    }

    /**
     * 通过字段列名查询多条记录
     *
     * @return key：表字段名，value：值
     */
    public List<Map<String, Object>> queryComplex(String sql, List<Object> params) throws SQLException {
        List<Map<String, Object>> result = new ArrayList<>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        for (Object param : params) {
            pstmt.setObject(index++, param);
        }
        resultSet = pstmt.executeQuery();

        ResultSetMetaData metaData = resultSet.getMetaData();
        int colLength = metaData.getColumnCount();
        while (resultSet.next()) {
            Map<String, Object> map = new HashMap<>(16);
            for (index = 1; index < colLength + 1; index++) {
                String colName = metaData.getColumnName(index);
                Object colsValue = resultSet.getObject(colName);
                map.put(colName, colsValue);
            }
            result.add(map);
        }
        this.release();
        return result;
    }

    /**
     * 通过反射机制查询多条记录
     *
     * @param cls 这里的Bean中字段是和数据库表中字段一致，没有驼峰简写
     */
    public <T> List<T> queryComplexRef(String sql, List<Object> params, Class<T> cls) throws SQLException,
            InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
        List<T> result = new ArrayList<>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        for (Object param : params) {
            pstmt.setObject(index++, param);
        }
        resultSet = pstmt.executeQuery();

        ResultSetMetaData metaData = resultSet.getMetaData();
        int colLength = metaData.getColumnCount();
        while (resultSet.next()) {
            // 通过反射机制创建一个实例
            T obj = cls.newInstance();
            for (index = 1; index < colLength + 1; index++) {
                String colName = metaData.getColumnName(index);
                Object colsValue = resultSet.getObject(colName);
                // 获取数据库表对应的Bean字段
                Field field = cls.getDeclaredField(colName);
                // 打开javabean的访问权限
                field.setAccessible(true);
                // 对象字段设置内容值
                field.set(obj, colsValue);
            }
            result.add(obj);
        }
        this.release();
        return result;
    }
}
